Skip to main content

SMS CDR

Document Version: 1.0 Last Updated: 25-01-2026 Maintained By: Niha

Table of Contents​

  1. Overview
  2. Table Schema
  3. Field Reference
  4. Business Logic
  5. Data Quality
  6. Appendix

Overview​

Summary​

This document provides detailed documentation for the SMS CDR table, which records all SMS messaging events with corresponding usage, charging, and network information for billing purposes.

The SMS CDR table captures detailed records of SMS messages. Usage is recorded as being either from Free Units (related to offerings) or as Charged PAYG usage. Free Units can either be joined with the PE_FREE_UNIT table (for the specific offering it came from) or the PE_FREE_UNIT_TYPE table (for bucket types).

Tables discussed​

Table NameDescriptionImportant Columns
SMS CDRMain CDR table for SMSCDR_ID, SUBSCRIBER_KEY
PE_FREE_UNITFree unit instances linked to offeringsFREE_UNIT_ID, OFFERING_ID
PE_FREE_UNIT_TYPEFree unit bucket typesFU_TYPE_ID, FU_TYPE_NAME
OfferingsOffering metadataOFFERING_ID

Table Schema​

Field Reference​

1. CDR Identifiers​

Fields that uniquely identify CDR records and sessions.

CDR_ID​

  • Description: Unique identifier for each CDR record, auto-generated by the CBS system
  • Business Logic: Combined with CDR_SUB_ID to form the complete primary key
  • Example: 123456789012345

CDR_SUB_ID​

  • Description: Sub-record identifier for split CDRs
  • Business Logic: Value starts at 0 for first record, increments for each split
  • Example: 0, 1, 2
  • Notes: SMS CDRs rarely split, most have CDR_SUB_ID = 0

SESSION_ID​

  • Description: Session identifier linking related CDRs
  • Business Logic:
    • For Hybrid subscribers: Multiple CDRs share same SESSION_ID
    • For split sessions: All parts share same SESSION_ID
    • Can be used to aggregate CDRs representing a single SMS event

CUST_LOCAL_START_DATE​

  • Description: SMS send time in local timezone

CUST_LOCAL_END_DATE​

  • Description: SMS completion time in local timezone

2. Subscriber Information​

Fields identifying the subscriber and account hierarchy.

PRI_IDENTITY​

  • Description: Primary identity - MSISDN (mobile number)
  • Format: International format without + symbol
  • Business Logic: The charged party's phone number

SUBSCRIBER_KEY​

  • Description: Service ID from CRM system
  • Business Logic: Unique identifier for the service instance, links to CRM
  • Example: 5001234567
  • Joins: Link to subscriber dimension tables

ACCOUNT_KEY​

  • Description: Payment account ID from CRM
  • Business Logic: Multiple subscribers can share same account (family plans)
  • Example: 3001234567
  • Joins: Link to account dimension tables

UserState​

  • Description: Numeric string of seven digits, in the format CCMMMMM. The first and second digits C indicate the life cycle state of a subscriber. The first digit C indicates the state of a prepaid subscriber, and the second digit C indicates the state of a postpaid subscriber. The options are as follows:
    • 0 - Idle
    • 1 - Active
    • 2 - Suspended
    • 3 - Disable
    • 4 - Pool
    • 5 - Pool without activation

GroupCallType​

  • Description: Corporate User Group (CUG) classification
ValueDescription
0Non-corporate usage
1Corporate intra-network
2Corporate inter-network
4Corporate off-net number group
12Corporate off-net number group

OBJ_TYPE​

  • Description: Object type being charged
  • Valid Values:
    • S - Subscriber (individual service)
    • A - Account (account-level charging)
    • G - Subscriber Group (for Connect offering)
  • Business Logic: Determines what entity the charge applies to
  • Example: S
  • Notes: For Connect offering, may be Account or Subscriber Group

OBJ_ID​

  • Description: Object identifier corresponding to OBJ_TYPE
  • Business Logic:
    • When OBJ_TYPE = 'S': OBJ_ID = SUBSCRIBER_KEY
    • When OBJ_TYPE = 'A': OBJ_ID = ACCOUNT_KEY
    • When OBJ_TYPE = 'G': OBJ_ID = Subscriber Group ID
  • Example: 5001234567

CallingPartyNumber​

  • Description: Calling/sending party number
  • Format Options:
    • Fixed-line: Country code + Area code + PSTN number
    • Mobile: Country code + MSISDN
    • URI: Format as reported by network

CalledPartyNumber​

  • Description: Called/receiving party number
  • Format Options:
    • Fixed-line: Country code + Area code + PSTN number
    • Mobile: Country code + MSISDN
    • URI: Format as reported by network

ChargingPartyNumber​

  • Description: Charged party number (who pays for the SMS)

3. Usage Metrics​

Fields capturing SMS usage volumes and measurements.

USAGE_MEASURE_ID​

  • Description: Unit of measurement for usage
  • Valid Values:
FU_MEASURE_ID
1003
1004
1006
1101
1106
1107
1108
1109
1121
1122
  • Business Logic: For SMS, typically uses 1101 (Item)

ACTUAL_USAGE​

  • Description: Actual usage volume consumed
  • Unit: Determined by USAGE_MEASURE_ID
  • Business Logic: For SMS, typically 1 per message

RATE_USAGE​

  • Description: Usage volume used for rating/charging
  • Unit: Same as ACTUAL_USAGE
  • Business Logic:
    • May differ from ACTUAL_USAGE due to rounding rules
    • Formula: RATE_USAGE >= ACTUAL_USAGE (after rounding up)
    • Used in charge calculation
  • Example: 1
  • Notes: For SMS, usually equals ACTUAL_USAGE (1 message = 1 charged unit)

FREE_UNIT_AMOUNT_OF_TIMES​

  • Description: Total free unit usage consumed (aggregated across all free unit instances)
  • Unit: Times/Items
  • Business Logic: Sum of CHG_AMOUNT across all 10 free unit ID groups

4. Charging Information​

Fields related to billing and charges applied.

DEBIT_AMOUNT​

  • Description: Total amount deducted from all account sources
  • Currency: MVR (Maldivian Rufiyaa)
  • Business Logic:
    • Formula: DEBIT_AMOUNT = DEBIT_FROM_PREPAID + DEBIT_FROM_POSTPAID
    • For Hybrid subscribers with 2 CDRs: Sum both CDRs for total charge
    • Represents PAYG (Pay As You Go) charges only
  • Example: 0.50
  • Revenue Calculation: This is the charged revenue field

DEBIT_FROM_PREPAID​

  • Description: Amount deducted from prepaid sources (main funds + bonuses)
  • Currency: MVR
  • Business Logic: Includes all prepaid account deductions
  • Example: 0.50

DEBIT_FROM_POSTPAID​

  • Description: Amount deducted from postpaid sources (credits, bonuses, deposits, prepayments)
  • Currency: MVR
  • Business Logic: Includes all postpaid account deductions
  • Example: 0.00
  • Hybrid Note: For Hybrid PayType, check both prepaid and postpaid amounts

5. Plan and Offerings​

Fields describing the subscriber's service plan and add-ons.

MainOfferingID​

  • Description: Primary offering ID (base plan)
  • Business Logic: The subscriber's main plan at time of CDR
  • Example: 100001
  • Joins: Link to offering dimension for plan details

LastEffectOffering​

  • Description: Last effective offering ID applied
  • Business Logic:
    • The offering that provided the free units consumed
    • Can be main plan, add-on, bonus, or special offer
  • Example: 100025

PayType​

  • Description: Payment type of subscriber
  • Valid Values:
ValueTypeCharging Behavior
0PrepaidDeducted from prepaid balance
1PostpaidAdded to postpaid invoice
2HybridGenerates 2 CDRs (prepaid + postpaid portions)
  • Business Logic:
    • Hybrid subscribers generate separate CDRs for prepaid and postpaid portions
    • Link Hybrid CDRs using SESSION_ID

6. Network Details​

Fields describing network and location information.

SERVICE_CATEGORY​

  • Description: Type of service/event
  • Valid Values:
    • SMS - SMS message (primary value for this CDR type)
    • [Other categories if applicable]
  • Example: SMS

SMSType​

  • Description: SMS classification based on destination
  • Valid Values:
    • local - Local SMS
    • intra-province - Within same province/region
    • inter-province - Between different provinces/regions
    • international toll - International SMS

OnNetIndicator​

  • Description: SMS network type indicator
  • Valid Values:
    • 0 - Local intra-network (on-net)
    • 1 - Inter-network (off-net)
    • 2 - Unknown
  • Example: 0
  • Business Logic: Identifies whether SMS is sent within same network or to different network
  • Use Cases: On-net vs off-net pricing analysis

CallingNetworkType​

  • Description: Access network type of calling party
  • Valid Values: GSM, CDMA, EUTRAN, PSTN, etc.
  • Example: GSM

CalledNetworkType​

  • Description: Access network type of called party
  • Valid Values: GSM, CDMA, EUTRAN, PSTN, etc.
  • Example: GSM

CallingCellID​

  • Description: Cell tower identifier for sending party
  • Business Logic: Location-based analysis and network coverage optimization
  • Example: CELL-MLE-001
  • Use Cases: SMS origin location analysis

CalledCellID​

  • Description: Cell tower identifier for receiving party
  • Business Logic: Location-based analysis and network coverage optimization
  • Example: CELL-MLE-002
  • Use Cases: SMS destination location analysis

CallingHomeCountryCode​

  • Description: Home country code of calling party
  • Example: 960 (Maldives)

CalledHomeCountryCode​

  • Description: Home country code of called party
  • Example: 960 (Maldives)

CallingHomeAreaNumber​

  • Description: Home area/region number of calling party
  • Example: 03 (for Addu City)

RoamState​

  • Description: Roaming state of charged party
  • Valid Values:
    • 0 - Home network
    • 1 - National roaming
    • 2 - International roaming
    • [Other values]
  • Example: 0
  • Business Logic: Impacts rating and charging rules for roaming SMS

7. Free Unit Details (Per Offering)​

Fields capturing free unit consumption broken down by offering. The CDR contains 10 sets of these fields (suffixed _1 through _10).

FREE_UNIT_ID_[1-10]​

  • Description: Unique ID of free unit instance
  • Business Logic:
    • Links to PE_FREE_UNIT table for offering details
    • Up to 10 different free unit instances can be consumed in one CDR
    • NULL if no free unit consumed in this slot
  • Join: JOIN PE_FREE_UNIT ON FREE_UNIT_ID_1 = PE_FREE_UNIT.FREE_UNIT_ID

CHG_AMOUNT_[1-10]​

  • Description: Free unit usage amount for this instance
  • Unit: Items/Times (for SMS CDRs)
  • Business Logic: The actual usage consumed from this free unit instance
  • Example: 1 (1 SMS)

FU_MEASURE_ID_[1-10]​

  • Description: Free unit measurement ID
  • Valid Values: Same as USAGE_MEASURE_ID table above (typically 1101 for Item or 1006 for Times)
  • Business Logic: Defines the unit of CHG_AMOUNT
  • Example: 1101 (Item)

Business Logic​

Usage Classification​

1. PAYG Usage (Charged)​

  • Definition: Usage beyond free unit allowances, charged per SMS
  • Calculation: RATE_USAGE - FREE_UNIT_AMOUNT_OF_TIMES

2. Baseplan Usage​

  • Definition: Usage covered by free units from base plan
  • Identification: Join with PE_FREE_UNIT_TYPE to get bucket type
  • Calculation: Sum CHG_AMOUNT per baseplan bucket

3. Addon Usage​

  • Definition: Usage covered by free units from add-ons
  • Identification: Join with PE_FREE_UNIT_TYPE to get bucket type
  • Calculation: Sum CHG_AMOUNT per add-on bucket

Bucket vs Offering Classification​

  • Bucket Classification: Join with PE_FREE_UNIT_TYPE for free unit bucket types (e.g., "Base SMS", "Addon SMS", "Bonus SMS")
  • Offering Classification: Join with PE_FREE_UNIT for specific offering instances
  • Both classifications can coexist for comprehensive analysis

Charge Calculation​

1. PAYG charge​

  • Definition: Charged incurred within the call
  • Calculation: Sum of debit amount

Special Scenarios​

Hybrid Subscriber Handling​

  • Scenario: Hybrid (PayType=2) subscribers generate 2 CDRs
  • Identification: Same SESSION_ID, different CDR_IDs
  • Charge Splitting: One CDR has DEBIT_FROM_PREPAID, other has DEBIT_FROM_POSTPAID
  • Total Charge: Sum both DEBIT_AMOUNT values

CUG SMS Flag​

  • Field: GroupCallType
  • Logic: GroupCallType != 0 for corporate SMS
  • Use Case: Add CUG flag & if OBJ_TYPE != 'S', the charge should not be accounted to the customer

Data Quality​

Validation Rules​

Mandatory Field Checks​

  • CDR_ID, CDR_SUB_ID, SESSION_ID, PRI_IDENTITY, SUBSCRIBER_KEY, ACCOUNT_KEY, ACTUAL_USAGE, RATE_USAGE, DEBIT_AMOUNT must not be NULL
  • CalledPartyNumber, ChargingPartyNumber cannot be empty

Appendix​

Table NamePurposeJoin Condition
PE_FREE_UNITFree unit instances and offering mappingsFREE_UNIT_ID_[1-10] = FREE_UNIT_ID
PE_FREE_UNIT_TYPEFree unit bucket typesVia PE_FREE_UNIT
[SUBSCRIBER]Subscriber attributesSUBSCRIBER_KEY
[ACCOUNT]Account attributesACCOUNT_KEY
[OFFERING]Offering/plan detailsMainOfferingID, LastEffectOffering, OpposeMainOfferingID

Change Log​

VersionDateAuthorChanges
1.02026-01-25NihaInitial documentation

Document End